/* GSProductStorageDao.java
 *
 * Project/Product: GWBS2009
 *
 * Modify Information:
 * =============================================================================
 *   Author	Date       Description
 *   ----------	---------- ---------------------------------------------------
 *   Administrator	2009-3-19	First release
 *
 * Licensed Materials - Property of APEP
 * =============================================================================
 *    (C) COPYRIGHT Shanghai Auto Mart E-Procurement Co., Ltd. 2007 - 2009
 *    All Rights Reserved.
 *
 *    This software is the confidential and proprietary information of
 *    Shanghai Auto Mart E-Procurement Co., Ltd. ("Confidential Information").
 *    You shall not disclose such Confidential Information and shall use it only
 *    in accordance with the terms of the license agreement you entered into
 *    with Shanghai Auto Mart E-Procurement Co., Ltd.
 *
 * Noties:
 * =============================================================================
 * 
 */
/**
 * apep查询库存及出入库记录
 * */
package com.hitrust.gs.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.ArrayList;

import com.hitrust.gs.bean.GSInOrOutStorageBean;
import com.hitrust.gs.bean.GSProductApepBean;
import com.hitrust.gs.bean.GSProductStorageApepBean;
import com.hitrust.gs.lib.db.GSBaseDAO;
import com.hitrust.gs.util.date.DateUtil;
import com.hitrust.gs.util.exception.GSException;
import com.hitrust.gs.vo.GSOrderAuditQueryVO;
import com.hitrust.gs.vo.ProductMapOldVO;

public class GSProductStorageDao extends GSBaseDAO {
	public static String IN_TYPE="1";//入库
	public static String OUT_TYPE="0";//出库
	public String getStorageSql(String providerCode,String[] porductIds,String isWarning){
		StringBuffer result=new StringBuffer();
		result.append("select a.*,b.apep_code,b.CONCENTRICITY_CODE,b.NAME,b.PRODUCT_BRAND,b.MODEL,");
		result.append("b.SPEC,b.QUALITY_ASSURE_PERIOD,b.MANUFATURER,b.AREA,b.ADAPT_MACHINE_TYPE,b.product_unit,");
		result.append("c.MIN_COUNT ,c.FILL_COUNT,c.MAX_COUNT from t_product_storage a,t_product b,T_WAREHOUSE_PRODUCT c where a.PRODUCT_ID=b.ID");
		result.append(" and  a.warehouse_code=c.warehouse_code and a.product_id=c.PRODUCT_ID");
		result.append(" and  b.PROVIDER_CODE='"+providerCode+"'");
		if(porductIds!=null&&porductIds.length>0){
			String tmp="";
			for (int i = 0; i < porductIds.length; i++) {
				tmp+="'"+porductIds[i]+"',";
			}
			result.append(" and a.product_code in ("+tmp.substring(0,tmp.length()-1)+")");
		}
		if("1".equals(isWarning)){
			result.append(" and a.product_count<=c.min_count ");
		}
		return result.toString();
	}
	//add leo.gan 2010-05-19 查询供应产品
	public String getProductsSql(String providerCode,String[] porductIds){
		StringBuffer result=new StringBuffer();
		result.append("select b.id,b.apep_code,b.CONCENTRICITY_CODE,b.NAME,b.PRODUCT_BRAND,b.MODEL,");
		result.append("b.SPEC,b.QUALITY_ASSURE_PERIOD,b.MANUFATURER,b.AREA,b.ADAPT_MACHINE_TYPE,b.product_unit,b.provider_code,b.balance_mode,b.is_buy,b.status,b.unit_price,b.DRAWING_PIECE_CODE");
		result.append(" from t_product b where  IS_BUY='1' AND STATUS='1' ");
		result.append(" and  b.PROVIDER_CODE='"+providerCode+"'");
		if(porductIds!=null&&porductIds.length>0){
			String tmp="";
			for (int i = 0; i < porductIds.length; i++) {
				tmp+="'"+porductIds[i]+"',";
			}
			result.append(" and b.CONCENTRICITY_CODE in ("+tmp.substring(0,tmp.length()-1)+") ");
		}

		result.append(" order by LAST_UPDATE_TIME desc");
		return result.toString();
	}
	//add end

	public String getStorageSql(GSOrderAuditQueryVO vo){
		StringBuffer result=new StringBuffer();
		result.append("select a.*,b.apep_code,b.CONCENTRICITY_CODE,b.NAME,b.PRODUCT_BRAND,b.MODEL,");
		result.append("b.SPEC,b.QUALITY_ASSURE_PERIOD,b.MANUFATURER,b.AREA,b.ADAPT_MACHINE_TYPE,b.product_unit,");
		result.append("c.MIN_COUNT,c.FILL_COUNT,c.MAX_COUNT from t_product_storage a,t_product b,T_WAREHOUSE_PRODUCT c where a.PRODUCT_ID=b.ID");
		result.append(" and  a.warehouse_code=c.warehouse_code and a.product_id=c.PRODUCT_ID");
		if (vo.deptId != null && vo.deptId.trim().length() > 0) {
			result.append(" and a.WAREHOUSE_CODE like'" + vo.deptId + "%'");
		}
		if (vo.concentricityId != null && vo.concentricityId.trim().length() > 0) {
			result.append(" and a.product_code like '%" + vo.concentricityId.trim() + "%'");
		}
		if (vo.manufInfo != null && vo.manufInfo.trim().length() > 0) {
			result.append(" and (b.NAME like '%" + vo.manufInfo.trim() + "%'");
			result.append(" or b.MODEL like '%" + vo.manufInfo.trim() + "%'");
			result.append(" or b.SPEC like '%" + vo.manufInfo.trim() + "%'");
			result.append(" or b.PRODUCT_BRAND like '%" + vo.manufInfo.trim() + "%'");
			result.append(" or b.APPENDER_PROPERTY like '%" + vo.manufInfo.trim() + "%'");
			result.append(" or b.MANUFATURER like '%" + vo.manufInfo.trim() + "%')");
		}
		result.append(" and a.product_count<=c.min_count and b.is_buy='1'");
		if (vo.showMinZero != null && vo.showMinZero.equals("Y")) {
			result.append(" and c.min_count>0 ");
		}
		return result.toString();
	}
	//查询库存中产品处理中订单数量
	public String getStorageOrderCountsql(String apepcode,String conid,String warecode){
		StringBuffer result=new StringBuffer();
		result.append("select sum(a.gs_order_count) as ordercount from gs_order_detail a,gs_order b");
		result.append(" where a.gs_order_detail_status in ('3','6','7','9','10')");
		result.append(" and  a.gs_order_tempid=b.gs_order_tempid and b.gs_workcorp_id ='"+warecode+"'" );
		result.append(" and a.gs_manufacture_id='"+apepcode+"' and a.gs_concentricity_id='"+conid+"'");
		return result.toString();
	}
	
	//查询前三个月平均出库数量
	public String getStorageOutCountsql(String productcode,String warehousecode){
		StringBuffer result=new StringBuffer();
		result.append("select sum(a.PRODUCT_COUNT) as productcount from t_out_sub a,t_out_head b ");
		result.append(" where a.out_bill_id=b.id  and a.status=1 and a.product_code='"+productcode+"' and b.warehouse_code='"+warehousecode+"'");
		String endTime=DateUtil.getCurrentDate("yyyy-MM");
		String startTime=getBeforeMonthStartStr(endTime);
		result.append(" and a.create_time>='"+startTime+"-01 00:00:00'");
		result.append(" and a.create_time<'"+endTime+"-01 00:00:00'");
		return result.toString();
	}
	
	
	public String getBeforeMonthStartStr(String curMonth){
		String year=curMonth.substring(0,4);
		String month=curMonth.substring(5, curMonth.length());
		String beforeMonth="";
		if("03".equals(month)){
			beforeMonth=(Integer.parseInt(year)-1)+"-12";
		}else if("02".equals(month)){
			beforeMonth=(Integer.parseInt(year)-1)+"-11";
		}else if("01".equals(month)){
			beforeMonth=(Integer.parseInt(year)-1)+"-10";
		}
		else{
			beforeMonth=year+"-0"+(Integer.parseInt(month)-3);
		}
		System.out.println("beforeMonth"+beforeMonth);
		return beforeMonth;
	}
	
	
	//分页查询库存
	public ArrayList getProductStorageListPage(String providerCode,String[] porductIds,String isWarning,String pagesize,String pageNo) throws Exception{
		String sql=this.getStorageSql(providerCode, porductIds, isWarning);
		Object[] tobj={};
		ArrayList tList=this.executeQuery(new GSProductStorageApepBean(), sql, tobj,Integer.parseInt(pageNo),Integer.parseInt(pagesize));
		return tList;
	}
	//add leo.gan 2010-05-19 查询供应产品分页查询供应产品
	public ArrayList getProductsListPage(String providerCode,String[] porductIds,String pagesize,String pageNo) throws Exception{
		String sql=getProductsSql(providerCode, porductIds);
		Object[] tobj={};
		ArrayList tList=this.executeQuery(new GSProductApepBean(), sql, tobj,Integer.parseInt(pageNo),Integer.parseInt(pagesize));
		return tList;
	}

	public int getProductsCount(String providerCode,String[] porductIds) throws Exception{
		String sql="select count(*) from ( "+getProductsSql(providerCode, porductIds)+")";
		Object[] tobj={};
		int count=this.queryRowNum(sql, tobj);
		return count;
	}
	public ArrayList getProductsList(String providerCode,String[] porductIds) throws Exception{
		String sql=getProductsSql(providerCode, porductIds);
		Object[] tobj={};
		ArrayList tList=this.executeQuery(new GSProductApepBean(), sql, tobj);
		return tList;
	}
	//add end

	public int getProductStorageCount(String providerCode,String[] porductIds,String isWarning) throws Exception{
		String sql="select count(*) from ( "+this.getStorageSql(providerCode, porductIds, isWarning)+")";
		Object[] tobj={};
		int count=this.queryRowNum(sql, tobj);
		return count;
	}
	//查询库存
	public ArrayList getProductStorageList(String providerCode,String[] porductIds,String isWarning) throws Exception{
		String sql=this.getStorageSql(providerCode, porductIds, isWarning);
		Object[] tobj={};
		ArrayList tList=this.executeQuery(new GSProductStorageApepBean(), sql, tobj);
		return tList;
	}
	//查询库存产品处理中订货数量
	public double getStorageOrderNum(String apepcode,String conid,String warecode){
		Connection con=getConnetion();
		String sql=getStorageOrderCountsql(apepcode, conid, warecode);
		double number=0;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=con.prepareStatement(sql);
			rs=ps.executeQuery();
			if(rs.next()){
				number=rs.getDouble(1);
			}
		} catch (SQLException e) {
		}finally{
			close(rs);
			close(ps);
			close(con);
		}
		return number;
	}
	
	
	//查询前3个月出库的月平均数量
	public String getStorageOutNum(String productcode,String warehousecode){
		Connection con=getConnetion();
		String sql=getStorageOutCountsql(productcode,warehousecode);
		double number=0;	
		DecimalFormat df = new DecimalFormat("##0.00");
		String totalAmount ="";
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=con.prepareStatement(sql);
			rs=ps.executeQuery();
			if(rs.next()){
				number=rs.getDouble(1)/3;
				totalAmount = df.format(number);
			}
		} catch (SQLException e) {
		}finally{
			close(rs);
			close(ps);
			close(con);
		}
		return totalAmount;
	}
	
	
	
	//分页查询库存显示
	public ArrayList getProductStorageViewListPage(GSOrderAuditQueryVO vo,String pageNo,String pagesize) throws Exception{
		String sql=this.getStorageSql(vo);
		Object[] tobj={};
		ArrayList tList=this.executeQuery(new GSProductStorageApepBean(), sql, tobj,Integer.parseInt(pageNo),Integer.parseInt(pagesize));
		return tList;
	}
	
	//查询库存显示
	public ArrayList getProductStorageViewList(GSOrderAuditQueryVO vo) throws Exception{
		String sql=this.getStorageSql(vo);
		Object[] tobj={};
		ArrayList tList=this.executeQuery(new GSProductStorageApepBean(), sql, tobj);
		return tList;
	}
	public int getProductStorageViewCount(GSOrderAuditQueryVO vo) throws Exception{
		String sql="select count(*) from ( "+this.getStorageSql(vo)+")";
		Object[] tobj={};
		int count=this.queryRowNum(sql, tobj);
		return count;
	}
	/********************************出入库查询****************************************/
	private String inOrOutStorageSql(GSInOrOutStorageBean inoutBean){
		StringBuffer result=new StringBuffer();
		String[] porductIds=null;
		if (!"".equals(inoutBean.getProductIds())) {
			porductIds=inoutBean.getProductIds().split(",");
		}
		if (IN_TYPE.equals(inoutBean.getType())) {
			result.append("select a.apep_code p1,a.name p2,b.product_code p3,c.warehouse_code p4,c.warehouse_name p5,");
			result.append("a.MODEL p6,a.SPEC p7,a.PRODUCT_BRAND p8,a.MANUFATURER p9,b.BALANCE_MODE p10,c.IN_TIME p11,");
			result.append("b.PRODUCT_COUNT p12,a.PRODUCT_UNIT p13 from t_product a,t_in_sub b,t_in_head c ");
			result.append("where a.ID=b.PRODUCT_ID and c.ID=b.IN_BILL_ID and c.PROVIDER_CODE='"+inoutBean.getProviderCode()+"' ");
			if(porductIds!=null&&porductIds.length>0){
				String tmp="";
				for (int i = 0; i < porductIds.length; i++) {
					tmp+="'"+porductIds[i]+"',";
				}
				result.append(" and b.product_code in ("+tmp.substring(0,tmp.length()-1)+") ");
			}
			if (!"".equals(inoutBean.getBeginDate())) {
				result.append(" and c.in_time>='"+inoutBean.getBeginDate()+"'");
			}
			if(!"".equals(inoutBean.getEndDate())){
				result.append(" and c.in_time<='"+inoutBean.getEndDate()+"'");
			}
			if(!"".equals(inoutBean.getBalanceMode())){
				result.append(" and b.BALANCE_MODE='"+inoutBean.getBalanceMode()+"'");
			}
		}else if(OUT_TYPE.equals(inoutBean.getType())){
			result.append("select a.apep_code p1,a.name p2,b.product_code p3,c.warehouse_code p4,c.warehouse_name p5,");
			result.append("a.MODEL p6,a.SPEC p7,a.PRODUCT_BRAND p8,a.MANUFATURER p9,b.BALANCE_MODE p10,c.OUT_TIME p11,");
			result.append("b.PRODUCT_COUNT p12,a.PRODUCT_UNIT p13 from t_product a,t_out_sub b,t_out_head c ");
			result.append("where a.ID=b.PRODUCT_ID and c.ID=b.OUT_BILL_ID and b.PROVIDER_CODE='"+inoutBean.getProviderCode()+"' ");
			if(porductIds!=null&&porductIds.length>0){
				String tmp="";
				for (int i = 0; i < porductIds.length; i++) {
					tmp+="'"+porductIds[i]+"',";
				}
				result.append(" and b.product_code in ("+tmp.substring(0,tmp.length()-1)+") ");
			}
			if (!"".equals(inoutBean.getBeginDate())) {
				result.append(" and c.OUT_TIME>='"+inoutBean.getBeginDate()+"'");
			}
			if(!"".equals(inoutBean.getEndDate())){
				result.append(" and c.OUT_TIME<='"+inoutBean.getEndDate()+"'");
			}
			if(!"".equals(inoutBean.getBalanceMode())){
				result.append(" and b.BALANCE_MODE='"+inoutBean.getBalanceMode()+"'");
			}
		}
		return result.toString();
	}
	//分页查询出入库信息
	public ArrayList getInOrOutStorageListPage(GSInOrOutStorageBean inoutBean,int pageNo,int pageSize) throws Exception{
		ArrayList tList=new ArrayList();
		Object[] obj={};
		tList=this.executeQuery(new GSInOrOutStorageBean(), inOrOutStorageSql(inoutBean), obj,pageNo,pageSize);
		return tList;
	}
	public ArrayList getInOrOutStorageList(GSInOrOutStorageBean inoutBean) throws Exception{
		ArrayList tList=new ArrayList();
		Object[] obj={};
		tList=this.executeQuery(new GSInOrOutStorageBean(), inOrOutStorageSql(inoutBean), obj);
		return tList;
	}
	public int getInOrOutStorageCount(GSInOrOutStorageBean inoutBean) throws Exception{
		String sql="select count(*) from ( "+inOrOutStorageSql(inoutBean)+")";
		Object[] tobj={};
		int count=this.queryRowNum(sql, tobj);
		return count;
	}
    public boolean insertProducStorage(String productCode) throws GSException {
        boolean bl = false;
        StringBuffer sb = new StringBuffer();
        sb.append(" insert into T_PRODUCT_SHARE_STOAGE values (" );
        sb.append("'"+productCode+"'");
        sb.append(")");
        try {
                bl = this.executeUpdate(sb.toString(), null, null, "");
            } catch (Exception e) {
                throw new GSException(GSException.E20042, GSException.E20042_MSG);
            }    
        return bl;
    }
}
